package com.han.dao.concrete.product;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import javax.naming.NamingException;

import com.han.bom.Exam;
import com.han.bom.PracticeExam;
import com.han.bom.RealExam;
import com.han.dao.abstraction.product.GenericExamDAO;

public class ExamDAO extends DataAccessObject implements GenericExamDAO {

	/**
	 * 
	 */
	private static final long serialVersionUID = -1261524917810022842L;

	public ArrayList<Exam> retrieveAllExams() throws ClassNotFoundException,
			SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		Exam exam = null;
		Boolean type = null;

		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	public Exam retrieveExamById(String examid) throws ClassNotFoundException,
			SQLException, NamingException {
		Exam exam = null;
		Boolean type = null;
		int eid = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam WHERE exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exam;
	}

	public ArrayList<Exam> retrieveExamsByProfessor(String profid)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		Exam exam = null;
		Boolean type = null;

		try {
			id = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam WHERE professor_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	public HashMap<RealExam, Double> retrieveRealExamsResults(String profid)
			throws ClassNotFoundException, SQLException, NamingException {
		HashMap<RealExam, Double> realexams = new HashMap<RealExam, Double>();
		int id = 0;
		Exam exam = null;
		Double average = new Double(0);

		try {
			id = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.*, AVG(b.exam_grade) AS average FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE a.professor_id=? AND a.exam_type = true GROUP BY a.exam_id ORDER BY b.date_taken DESC";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				exam = new RealExam();
				exam.setExamID("E" + this.resultSet.getInt("exam_id") + "RE");
				exam.setExamName(this.resultSet.getString("exam_name"));
				exam.setDuration(this.resultSet.getLong("exam_duration"));
				exam.setPassingPercent(this.resultSet.getDouble("exam_passing"));
				((RealExam) exam).setActivated(this.resultSet
						.getBoolean("exam_active"));
				exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
				((RealExam) exam).setTimesActivated(this.resultSet
						.getInt("activate_count"));
				average = this.resultSet.getDouble("average");

				realexams.put((RealExam) exam, average);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return realexams;
	}

	public HashMap<PracticeExam, Double> retrievePracticeExamsResults(
			String profid) throws ClassNotFoundException, SQLException,
			NamingException {
		HashMap<PracticeExam, Double> practiceexams = new HashMap<PracticeExam, Double>();
		int id = 0;
		Exam exam = null;
		Double average = new Double(0);

		try {
			id = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.*, AVG(b.exam_grade) AS average FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id=b.exam_id WHERE a.professor_id=? AND a.exam_type = false GROUP BY a.exam_id ORDER BY b.date_taken DESC";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				exam = new RealExam();
				exam.setExamID("E" + this.resultSet.getInt("exam_id") + "PE");
				exam.setExamName(this.resultSet.getString("exam_name"));
				exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
				average = this.resultSet.getDouble("average");

				practiceexams.put((PracticeExam) exam, average);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return practiceexams;
	}

	public ArrayList<Exam> retrieveExamsByStudent(String studentid)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		Exam exam = null;
		Boolean type = null;

		try {
			id = Integer.parseInt(studentid.substring(1));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM tbl_exam AS a INNER JOIN tbl_student_exam AS b ON a.exam_id = b.exam_id WHERE b.student_id=?";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, id);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	public ArrayList<Exam> retrieveExamsByKeyword(String key, String profId)
			throws ClassNotFoundException, SQLException, NamingException {
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int profid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			profid = Integer.parseInt(profId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			profid = 0;
		}

		try {
			this.connect();
			String select = "SELECT DISTINCT a.* FROM tbl_exam AS a INNER JOIN tbl_exam_questions AS b ON a.exam_id=b.exam_id INNER JOIN tbl_question AS c ON b.question_id=c.question_id INNER JOIN tbl_choice AS d ON c.question_id=d.question_id WHERE a.professor_id = 1 AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?) OR UPPER(c.question) LIKE UPPER(?) OR UPPER(c.question_category) LIKE UPPER(?) OR UPPER(d.answer) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, profid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.preparedStatement.setString(4, intkey);
			this.preparedStatement.setString(5, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	@Deprecated
	public ArrayList<Exam> retrieveExamsByKeywordForStudent(String key,
			String studentId) throws ClassNotFoundException, SQLException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int studentid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT * FROM ((SELECT a.exam_id,a.student_id FROM tbl_exam_response AS a RIGHT OUTER JOIN tbl_student_exam AS b on a.student_id=b.student_id GROUP BY a.exam_id) UNION (SELECT a.exam_id,a.student_id FROM tbl_exam_response AS a LEFT OUTER JOIN tbl_student_exam AS b on a.student_id=b.student_id GROUP BY a.exam_id)) AS c INNER JOIN tbl_exam AS d ON c.exam_id=d.exam_id WHERE c.student_id=? AND (d.exam_id = ? OR UPPER(d.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;

	}

	@Deprecated
	public ArrayList<Exam> retrieveMissedRealExamsByKeyword(String key,
			String studentId) throws ClassNotFoundException, SQLException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int studentid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.* FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id = b.exam_id WHERE b.student_id = ? AND b.istaken = false AND a.exam_type = true AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	@Deprecated
	public ArrayList<Exam> retrieveRealExamsTakenByKeyword(String key,
			String studentId) throws ClassNotFoundException, SQLException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int studentid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.* FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id = b.exam_id WHERE b.student_id = ? AND b.istaken = true AND a.exam_type = true AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	@Override
	@Deprecated
	public ArrayList<Exam> retrieveRealExamsNotTakenByKeyword(String key,
			String studentId) throws ClassNotFoundException, SQLException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int studentid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.* FROM tbl_exam AS a INNER JOIN tbl_student_exam AS b ON a.exam_id=b.exam_id WHERE b.student_id = ? AND a.exam_type = true AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	@Deprecated
	public ArrayList<Exam> retrievePracticeExamsTakenByKeyword(String key,
			String studentId) throws ClassNotFoundException, SQLException,
			NamingException {
		// TODO Auto-generated method stub
		ArrayList<Exam> exams = new ArrayList<Exam>();
		int id = 0;
		int studentid = 0;
		Exam exam = null;
		boolean type = false;
		String intkey = "%" + key + "%";

		try {
			if (key.contains("E") && (key.contains("PE") || key.contains("RE"))) {
				id = Integer.parseInt(key.replaceAll("[^\\d]", ""));
			}
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			studentid = Integer.parseInt(studentId.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			studentid = 0;
		}

		try {
			this.connect();
			String select = "SELECT a.* FROM tbl_exam AS a INNER JOIN tbl_exam_response AS b ON a.exam_id = b.exam_id WHERE b.student_id = ? AND a.exam_type = false AND (a.exam_id = ? OR UPPER(a.exam_name) LIKE UPPER(?))";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.preparedStatement.setInt(1, studentid);
			this.preparedStatement.setInt(2, id);
			this.preparedStatement.setString(3, intkey);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				type = this.resultSet.getBoolean("exam_type");

				if (type) {
					exam = new RealExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "RE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDuration(this.resultSet.getLong("exam_duration"));
					exam.setPassingPercent(this.resultSet
							.getDouble("exam_passing"));
					((RealExam) exam).setActivated(this.resultSet
							.getBoolean("exam_active"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));
					((RealExam) exam).setTimesActivated(this.resultSet
							.getInt("activate_count"));

					exams.add(exam);
				} else {
					exam = new PracticeExam();
					exam.setExamID("E" + this.resultSet.getInt("exam_id")
							+ "PE");
					exam.setExamName(this.resultSet.getString("exam_name"));
					exam.setDraft(this.resultSet.getBoolean("exam_is_draft"));

					exams.add(exam);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return exams;
	}

	public boolean insertExam(Exam exam, String profid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String insert = "";
		int pid = 0;

		try {
			pid = Integer.parseInt(profid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			pid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			if (exam instanceof RealExam) {
				insert = "INSERT INTO tbl_exam(exam_name,exam_duration,exam_passing,exam_type,exam_active,exam_is_draft,professor_id) VALUES(?,?,?,?,?,?,?)";
				this.preparedStatement = this.connection
						.prepareStatement(insert);

				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setLong(2, exam.getDuration());
				this.preparedStatement.setDouble(3, exam.getPassingPercent());
				this.preparedStatement.setBoolean(4, true);
				this.preparedStatement.setBoolean(5,
						((RealExam) exam).isActivated());
				this.preparedStatement.setBoolean(6, exam.isDraft());
				this.preparedStatement.setInt(7, pid);
			} else if (exam instanceof PracticeExam) {
				insert = "INSERT INTO tbl_exam(exam_name,exam_type,exam_active,exam_is_draft,professor_id) VALUES(?,?,?,?,?)";
				this.preparedStatement = this.connection
						.prepareStatement(insert);

				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setBoolean(2, false);
				this.preparedStatement.setBoolean(3, true);
				this.preparedStatement.setBoolean(4, exam.isDraft());
				this.preparedStatement.setInt(5, pid);
			}

			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean updateExam(Exam exam) throws ClassNotFoundException,
			SQLException, NamingException {
		boolean success = false;
		String update = "";
		this.connect();
		int examid = 0;

		try {
			examid = Integer
					.parseInt(exam.getExamID().replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			examid = 0;
		}

		try {
			this.connection.setAutoCommit(false);
			if (exam instanceof RealExam) {
				update = "UPDATE tbl_exam SET exam_name=?,exam_duration=?,exam_passing=?,exam_type=?,exam_active=?,exam_is_draft=? WHERE exam_id=?";
				this.preparedStatement = this.connection
						.prepareStatement(update);

				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setLong(2, exam.getDuration());
				this.preparedStatement.setDouble(3, exam.getPassingPercent());
				this.preparedStatement.setBoolean(4, true);
				this.preparedStatement.setBoolean(5,
						((RealExam) exam).isActivated());
				this.preparedStatement.setBoolean(6, exam.isDraft());
				this.preparedStatement.setInt(7, examid);
			} else if (exam instanceof PracticeExam) {
				update = "UPDATE tbl_exam SET exam_name=?,exam_type=?,exam_active=?,exam_is_draft=? WHERE exam_id=?";
				this.preparedStatement = this.connection
						.prepareStatement(update);

				this.preparedStatement.setString(1, exam.getExamName());
				this.preparedStatement.setBoolean(2, false);
				this.preparedStatement.setBoolean(3, true);
				this.preparedStatement.setBoolean(4, exam.isDraft());
				this.preparedStatement.setInt(5, examid);
			}
			int rows = this.preparedStatement.executeUpdate();
			success = (rows > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean deleteExam(String examid) throws ClassNotFoundException,
			SQLException, NamingException {
		boolean success = false;
		String delete = "";
		this.connect();
		int id = 0;

		try {
			id = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			this.connection.setAutoCommit(false);
			delete = "DELETE FROM tbl_exam WHERE exam_id=?";
			this.preparedStatement = this.connection.prepareStatement(delete);

			this.preparedStatement.setInt(1, id);
			int rows = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (rows > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean addStudentToExam(String studentid, String examid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String insert = "REPLACE INTO tbl_student_exam(student_id,exam_id) VALUES((SELECT user_id FROM tbl_user WHERE user_id=?),(SELECT exam_id FROM tbl_exam WHERE exam_id=?))";
		int id = 0;
		int eid = 0;

		try {
			id = Integer.parseInt(studentid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(insert);

			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, eid);

			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public boolean removeStudentFromExam(String studentid, String examid)
			throws ClassNotFoundException, SQLException, NamingException {
		boolean success = false;
		String delete = "DELETE FROM tbl_student_exam WHERE student_id=(SELECT user_id FROM tbl_user WHERE user_id=?) AND exam_id=(SELECT exam_id FROM tbl_exam WHERE exam_id=?)";
		int id = 0;
		int eid = 0;

		try {
			id = Integer.parseInt(studentid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			id = 0;
		}

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(delete);

			this.preparedStatement.setInt(1, id);
			this.preparedStatement.setInt(2, eid);

			int row = this.preparedStatement.executeUpdate();

			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}

	public int retrieveMaxExamID() throws ClassNotFoundException, SQLException,
			NamingException {
		int id = 0;

		try {
			this.connect();
			String select = "SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'beta_test_engine' AND TABLE_NAME = 'tbl_exam'";
			this.preparedStatement = this.connection.prepareStatement(select);
			this.resultSet = this.preparedStatement.executeQuery();

			while (resultSet.next()) {
				id = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return id;
	}

	public int retrieveActivateCount(String examid) throws SQLException,
			ClassNotFoundException, NamingException {
		int activatecount = 0;
		String count = "SELETE activate_count FROM tbl_exam WHERE exam_id=?";
		int eid = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.preparedStatement = this.connection.prepareStatement(count);

			this.preparedStatement.setInt(1, eid);
			this.resultSet = this.preparedStatement.executeQuery();

			while (this.resultSet.next()) {
				activatecount = this.resultSet.getInt(1);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.disconnect();
		}

		return activatecount;
	}

	public boolean updateActivateCount(String examid) throws SQLException,
			ClassNotFoundException, NamingException {
		boolean success = false;
		String update = "UPDATE tbl_exam SET activate_count = activate_count+1 WHERE exam_id=?";
		int eid = 0;

		try {
			eid = Integer.parseInt(examid.replaceAll("[^\\d]", ""));
		} catch (NumberFormatException e) {
			eid = 0;
		}

		try {
			this.connect();
			this.connection.setAutoCommit(false);
			this.preparedStatement = this.connection.prepareStatement(update);

			this.preparedStatement.setInt(1, eid);
			int row = this.preparedStatement.executeUpdate();
			this.connection.commit();
			success = (row > 0) ? true : false;
		} catch (SQLException e) {
			this.connection.rollback();
			logger.error(e.getMessage(), e);
			throw e;
		} finally {
			this.connection.setAutoCommit(true);
			this.disconnect();
		}

		return success;
	}
}